package com.engine.qz.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.thread.ThreadUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.api.browser.bean.SearchConditionGroup;
import com.api.browser.bean.SearchConditionItem;
import com.api.browser.bean.SearchConditionOption;
import com.cloudstore.eccom.constant.WeaBoolAttr;
import com.cloudstore.eccom.pc.table.WeaTable;
import com.cloudstore.eccom.pc.table.WeaTableColumn;
import com.cloudstore.eccom.pc.table.WeaTableType;
import com.cloudstore.eccom.result.WeaResultMsg;
import com.engine.common.util.ServiceUtil;
import com.engine.core.impl.Service;
import com.engine.qz.entity.CustomTemplateVo;
import com.engine.qz.entity.TemplateFieldsPo;
import com.engine.qz.service.CustomRosterService;
import com.engine.qz.service.HrmCustomTemplateService;
import com.engine.qz.util.SearchItemUtil;
import com.weaver.general.TimeUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.jetbrains.annotations.NotNull;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;
import weaver.general.GCONST;
import weaver.general.PageIdConst;
import weaver.general.ThreadPoolUtil;
import weaver.general.Util;
import weaver.hrm.User;

import javax.activation.MimetypesFileTypeMap;
import javax.ws.rs.core.Response;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.locks.ReentrantLock;
import java.util.stream.Collectors;

public class CustomRosterServiceImpl extends Service implements CustomRosterService {
    private BaseBean bb = new BaseBean();
    // 导出锁对象
    private static final Object exportLock = new Object();
    // 用户导出状态跟踪（防止同一用户重复提交）
    private final Map<String, Long> userExportStatus = new ConcurrentHashMap<>();
    private static final long USER_EXPORT_INTERVAL = 30000; // 30秒内不允许重复导出

    private HrmCustomTemplateService getService(User user) {
        return ServiceUtil.getService(HrmCustomTemplateServiceImpl.class, user);
    }

    @Override
    public Map<String, Object> getSearchCondition(Map<String, Object> params) {
        Map<String, Object> apiDatas = new HashMap<>();
        List<SearchConditionGroup> addGroups = new ArrayList<>();
        List<SearchConditionItem> conditionItems = new ArrayList<>();

        conditionItems.add(SearchItemUtil.browserItem(user, 2, 16, 2, false, "用工性质", "31", "USEKIND", ""));
        conditionItems.add(SearchItemUtil.datePickerItem(user, 2, 16, false, 2, "入职日期", "COMPANYSTARTDATEStart", "COMPANYSTARTDATEEnd"));
        conditionItems.add(SearchItemUtil.datePickerItem(user, 2, 16, false, 2, "离职日期", "lzrqStart", "lzrqEnd"));
        conditionItems.add(SearchItemUtil.datePickerItem(user, 2, 16, false, 2, "转正日期", "ZZRQStart", "ZZRQEnd"));
        conditionItems.add(SearchItemUtil.inputItem(user, 2, 16, 2, 16, "所有组织", "SYZZ"));
        conditionItems.add(SearchItemUtil.browserItem(user, 2, 16, 2, false, "一级分部", "169", "FB1", ""));
        conditionItems.add(SearchItemUtil.browserItem(user, 2, 16, 2, false, "二级分部", "169", "FB2", ""));
        conditionItems.add(SearchItemUtil.browserItem(user, 2, 16, 2, false, "三级分部", "169", "FB3", ""));
        conditionItems.add(SearchItemUtil.browserItem(user, 2, 16, 2, false, "一级部门", "167", "BM1", ""));
        conditionItems.add(SearchItemUtil.browserItem(user, 2, 16, 2, false, "二级部门", "167", "BM2", ""));
        conditionItems.add(SearchItemUtil.browserItem(user, 2, 16, 2, false, "三级部门", "167", "BM3", ""));
        conditionItems.add(SearchItemUtil.browserItem(user, 2, 16, 2, false, "岗位名称", "161", "GWMC", "JCL_GW_ALL"));
        conditionItems.add(SearchItemUtil.inputItem(user, 2, 16, 2, 16, "岗位名称文本", "bdhgwmc"));

        List<SearchConditionOption> selectOptions = new ArrayList<>();
        selectOptions.add(new SearchConditionOption("0", "初级"));
        selectOptions.add(new SearchConditionOption("1", "中级"));
        selectOptions.add(new SearchConditionOption("2", "高级"));
        selectOptions.add(new SearchConditionOption("3", "资深"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions, 2, 16, 6, false, "岗位等级", "GWDJ"));
        conditionItems.add(SearchItemUtil.browserItem(user, 2, 16, 2, false, "员工职级", "161", "YGZJ", "JCL_ZJ"));
        conditionItems.add(SearchItemUtil.inputItem(user, 2, 16, 2, 16, "司龄", "COMPANYWORKYEAR"));
        conditionItems.add(SearchItemUtil.inputItem(user, 2, 16, 2, 16, "岗龄", "GL"));
        conditionItems.add(SearchItemUtil.inputItem(user, 2, 16, 2, 16, "工龄", "WORKYEAR"));
        conditionItems.add(SearchItemUtil.browserItem(user, 2, 16, 2, false, "合同主体", "161", "HTGSMC", "JCL_HTZT"));
        conditionItems.add(SearchItemUtil.datePickerItem(user, 2, 16, false, 2, "合同日期", "HTQSRQStart", "HTZZRQEnd"));
        conditionItems.add(SearchItemUtil.inputItem(user, 2, 16, 2, 16, "签订次数", "QDCS"));
        List<SearchConditionOption> selectOptions16 = new ArrayList<>();
        selectOptions16.add(new SearchConditionOption("0", "是"));
        selectOptions16.add(new SearchConditionOption("1", "否"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions16, 2, 16, 6, false, "是否考核期", "SFKHQ"));
        List<SearchConditionOption> selectOptions4 = new ArrayList<>();
        selectOptions4.add(new SearchConditionOption("0", "男"));
        selectOptions4.add(new SearchConditionOption("1", "女"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions4, 2, 16, 6, false, "性别", "SEX"));
        conditionItems.add(SearchItemUtil.datePickerItem(user, 2, 16, false, 2, "进入集团日期", "JRJTRQStart", "JRJTRQEnd"));
        conditionItems.add(SearchItemUtil.browserItem(user, 2, 16, 2, false, "第一学历", "161", "EDUCATIONLEVEL", "XL_CUSTOM"));
        conditionItems.add(SearchItemUtil.browserItem(user, 2, 16, 2, false, "最高学历", "161", "DEGREE", "XL_CUSTOM"));
        List<SearchConditionOption> selectOptions5 = new ArrayList<>();
        selectOptions5.add(new SearchConditionOption("0", "已婚"));
        selectOptions5.add(new SearchConditionOption("1", "未婚"));
        selectOptions5.add(new SearchConditionOption("2", "离异"));
        selectOptions5.add(new SearchConditionOption("3", "丧偶"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions5, 2, 16, 6, false, "婚姻状况", "MARITALSTATUS"));
        conditionItems.add(SearchItemUtil.inputItem(user, 2, 16, 2, 16, "子女数量", "ZLSL"));
        conditionItems.add(SearchItemUtil.datePickerItem(user, 2, 16, false, 2, "出生日期", "BIRTHDAYStart", "BIRTHDAYEnd"));
        List<SearchConditionOption> selectOptions2 = new ArrayList<>();
        selectOptions2.add(new SearchConditionOption("0", "阳历"));
        selectOptions2.add(new SearchConditionOption("1", "阴历"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions2, 2, 16, 6, false, "阴历/阳历", "SRRQLX"));
        conditionItems.add(SearchItemUtil.inputItem(user, 2, 16, 2, 16, "政治面貌", "POLICY"));
        conditionItems.add(SearchItemUtil.inputItem(user, 2, 16, 2, 16, "民族", "FOLK"));
        conditionItems.add(SearchItemUtil.datePickerItem(user, 2, 16, false, 2, "参加工作日期", "WORKSTARTDATEStart", "WORKSTARTDATEEnd"));
        conditionItems.add(SearchItemUtil.inputItem(user, 2, 16, 2, 16, "是否签订劳动合同", "SFQDHT"));
        List<SearchConditionOption> selectOptions6 = new ArrayList<>();
        selectOptions6.add(new SearchConditionOption("0", "是"));
        selectOptions6.add(new SearchConditionOption("1", "否"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions6, 2, 16, 6, false, "是否干部", "SFGB"));
        List<SearchConditionOption> selectOptions7 = new ArrayList<>();
        selectOptions7.add(new SearchConditionOption("0", "聘"));
        selectOptions7.add(new SearchConditionOption("1", "任"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions7, 2, 16, 6, false, "干部情形", "GBQX"));
        List<SearchConditionOption> selectOptions8 = new ArrayList<>();
        selectOptions8.add(new SearchConditionOption("0", "稳定"));
        selectOptions8.add(new SearchConditionOption("1", "不稳定"));
        selectOptions8.add(new SearchConditionOption("2", "淘汰"));
        selectOptions8.add(new SearchConditionOption("3", "已有离职意向或明确提出离职"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions8, 2, 16, 6, false, "稳定度", "WDD"));
        List<SearchConditionOption> selectOptions9 = new ArrayList<>();
        selectOptions9.add(new SearchConditionOption("0", "可立马提拔"));
        selectOptions9.add(new SearchConditionOption("1", "0~6个月可提拔"));
        selectOptions9.add(new SearchConditionOption("2", "6~12个月可提拔"));
        selectOptions9.add(new SearchConditionOption("3", "1~2年可提拔，需要调整"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions9, 2, 16, 6, false, "继任者成熟度", "JRZCSD"));
        List<SearchConditionOption> selectOptions10 = new ArrayList<>();
        selectOptions10.add(new SearchConditionOption("0", "强烈-全国范围"));
        selectOptions10.add(new SearchConditionOption("1", "能接受-但要看城市"));
        selectOptions10.add(new SearchConditionOption("2", "能接受-但有条件"));
        selectOptions10.add(new SearchConditionOption("3", "一般-不太会考虑"));
        selectOptions10.add(new SearchConditionOption("4", "不接受"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions10, 2, 16, 6, false, "异地意愿度", "YDYYD"));
        List<SearchConditionOption> selectOptions11 = new ArrayList<>();
        selectOptions11.add(new SearchConditionOption("0", "自招"));
        selectOptions11.add(new SearchConditionOption("1", "外招"));
        selectOptions11.add(new SearchConditionOption("2", "内推"));
        selectOptions11.add(new SearchConditionOption("3", "外包"));
        selectOptions11.add(new SearchConditionOption("4", "召回"));
        selectOptions11.add(new SearchConditionOption("5", "校招"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions11, 2, 16, 6, false, "招聘来源", "ZPLY"));
        List<SearchConditionOption> selectOptions12 = new ArrayList<>();
        selectOptions12.add(new SearchConditionOption("0", "是"));
        selectOptions12.add(new SearchConditionOption("1", "否"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions12, 2, 16, 6, false, "是否退伍军人", "sfstwjr"));
        List<SearchConditionOption> selectOptions14 = new ArrayList<>();
        selectOptions14.add(new SearchConditionOption("0", "是"));
        selectOptions14.add(new SearchConditionOption("1", "否"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions14, 2, 16, 6, false, "是否二次入职", "SFECRZ"));
        List<SearchConditionOption> selectOptions13 = new ArrayList<>();
        selectOptions13.add(new SearchConditionOption("0", "主动离职"));
        selectOptions13.add(new SearchConditionOption("1", "被动离职"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions13, 2, 16, 6, false, "离职类型", "lizhiyewuleixing"));
        List<SearchConditionOption> selectOptions15 = new ArrayList<>();
        selectOptions15.add(new SearchConditionOption("0", "无"));
        selectOptions15.add(new SearchConditionOption("1", "有"));
        conditionItems.add(SearchItemUtil.selectItem(user, selectOptions15, 2, 16, 6, false, "有无薪资", "ywxz"));
        conditionItems.add(SearchItemUtil.inputItem(user, 2, 16, 2, 16, "办公地点", "field64"));

        addGroups.add(new SearchConditionGroup("全部条件", true, conditionItems));
        apiDatas.put("defaultcondition", addGroups);
        apiDatas.put("condition", addGroups);
        return apiDatas;
    }

    @Override
    public Response customExport(Map<String, Object> paramMap) {
        // 用户级防重复提交检查
        long currentTime = System.currentTimeMillis();
        Long lastExportTime = userExportStatus.get(String.valueOf(user.getUID()));
        if (lastExportTime != null && (currentTime - lastExportTime) < USER_EXPORT_INTERVAL) {
            return Response.ok("操作过于频繁，请" +
                    (USER_EXPORT_INTERVAL - (currentTime - lastExportTime)) / 1000 + "秒后再试").build();
        }
        userExportStatus.put(String.valueOf(user.getUID()), currentTime);

        synchronized (exportLock) {
            //清理今日之前的废文件
            cleanFiles();
            RecordSet rs = new RecordSet();
            //使用中的模板列字段
            String sql = "WITH SplitFields AS (\n" + "    SELECT \n" +
                    "        a.id AS templateId,\n" + "        TRY_CONVERT(int, value) AS fieldId,\n" +
                    "        ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY (SELECT NULL)) AS fieldOrder\n" + "    FROM uf_custom_template a\n" +
                    "    CROSS APPLY STRING_SPLIT(a.templateFields, ',') AS ids\n" + "    WHERE a.belongto = ? AND a.isused = 1\n" + ")\n" +
                    "SELECT \n" + "    b.id,\n" + "    b.chinese,\n" + "    b.dataName,\n" + "    b.transMethod\n" + "FROM SplitFields sf\n" +
                    "JOIN uf_roster_field b ON sf.fieldId = b.id\n" + "ORDER BY sf.templateId, sf.fieldOrder";
            rs.executeQuery(sql, user.getUID());
            LinkedList<String> fieldsList = new LinkedList<>();
            //需要转文本的游标map
            HashMap<Integer, String> hasTransMethod = new HashMap<>();
            //表头
            List<String> head = new LinkedList<>();
            //游标
            int cursor = 0;
            while (rs.next()) {
                String chinese = rs.getString("chinese");
                String dataName = rs.getString("dataName");
                String transMethod = rs.getString("transMethod");
                head.add(chinese);
                fieldsList.add(dataName);
                if (StringUtils.isNotEmpty(transMethod)) {
                    hasTransMethod.put(cursor, transMethod);
                }
                cursor++;
            }
            //导出的查询sql
            sql = "select top 1 sql from uf_roster_tempfield where userid=? order by id desc";
            rs.executeQuery(sql, user.getUID());
            if (rs.next()) {
                String fromSql = rs.getString("sql");
                //分页导出
                return pageExport(fieldsList, fromSql, head, hasTransMethod);

            } else {
                return null;
            }
        }
    }

    private void cleanFiles() {
        try {
            String directoryPath = "filesystem/roster/";
            String dateFormat = TimeUtil.dateAdd(TimeUtil.getCurrentDateString(), -1).replaceAll("-", "");
            File directory = new File(directoryPath);
            File[] files = directory.listFiles();
            if (files != null) {
                for (File file : files) {
                    String fileName = file.getName();
                    if (fileName.contains(dateFormat)) {
                        file.delete();
                    }
                }
            }
        } catch (Exception e) {
        }
    }

    private Response pageExport(LinkedList<String> fieldsList, String fromSql, List<String> head, HashMap<Integer, String> hasTransMethod) {
        // 使用SXSSFWorkbook处理大数据量，设置内存中保留的行数
        SXSSFWorkbook workbook = new SXSSFWorkbook(5000); // 保持100行在内存中，其余写入磁盘
        try {
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

            RecordSet rs = new RecordSet();

            String sql = "select " + String.join(",", fieldsList) + " " + fromSql;
            rs.execute(sql);
            List<List<String>> data = new ArrayList<>();
            while (rs.next()) {
                List<String> row = new ArrayList<>();
                for (String colName : fieldsList) {
                    String colValue = Util.null2String(rs.getString(colName));
                    int index = fieldsList.indexOf(colName);
                    if (hasTransMethod.containsKey(index) && StringUtils.isNotEmpty(colValue)) {
                        String txtValue = invokeMethod(hasTransMethod.get(index), colValue);
                        row.add(txtValue);
                    } else {
                        row.add(colValue);
                    }
                }
                data.add(row);
            }

            Sheet sheet = workbook.createSheet("sheet1");

            // 创建表头样式
            CellStyle headerStyle = createHeaderStyle(workbook);

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            // 写入表头
            for (int i = 0; i < head.size(); i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(head.get(i));
                cell.setCellStyle(headerStyle);
            }
            // 创建数据样式
            CellStyle dataStyle = createDataStyle(workbook);

            // 写入数据
            int rowNum = 1;
            for (int i = 0; i < data.size(); i++) {
                Row row = sheet.createRow(rowNum++);
                List<String> strings = data.get(i);
                for (int i1 = 0; i1 < strings.size(); i1++) {
                    Cell cell = row.createCell(i1);
                    cell.setCellStyle(dataStyle);
                    String value = strings.get(i1);
                    cell.setCellValue(value);
                    // 应用数据样式
                    cell.setCellStyle(dataStyle);
                }
                // 定期刷新行到磁盘以保持内存使用量低
                if (rowNum % 5000 == 0) {
                    ((SXSSFSheet) sheet).flushRows(5000); // 保留100行在内存中
                }
            }
            String xlsName = user.getUID() + "staff" + TimeUtil.getFormartString(new Date(), "yyyyMMddHHmmss") + ".xlsx";
            // 写入响应流
            String outPath = GCONST.getRootPath() + "filesystem" + File.separator + "roster" + File.separator + xlsName;
            String fileName = URLEncoder.encode("员工花名册-" + TimeUtil.getFormartString(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
            try (OutputStream out = new FileOutputStream(outPath)) {
                workbook.write(out);
                out.flush(); // 确保数据被刷新
            } finally {
                // 清理临时文件
                workbook.dispose();
            }
            File zipFile = new File(outPath);
            String mt = new MimetypesFileTypeMap().getContentType(zipFile);
            return Response.ok(zipFile, mt).header("Content-disposition", "attachment;filename=" + fileName).header("ragma", "No-cache").header("Cache-Control", "no-cache").build();

        } catch (Exception e) {
            bb.writeLog("pageExport-e-" + e.getMessage());
        }
        return null;
    }

    private String invokeMethod(String method, String value) {
        try {
            // 获取最后一个 "." 的位置
            int lastDotIndex = method.lastIndexOf('.');
            // 截取类名（从开始到最后一个 "." 之前）
            String className = method.substring(0, lastDotIndex);
            // 截取方法名（从最后一个 "." 之后到末尾）
            String methodName = method.substring(lastDotIndex + 1);
            // 加载类
            Class<?> clazz = Class.forName(className);
            // 获取方法，参数为String类型
            Method methodInst = clazz.getMethod(methodName, String.class);
            // 创建实例(假设类有无参构造函数)
            Object instance = clazz.getDeclaredConstructor().newInstance();
            // 调用方法并返回结果
            String result = (String) methodInst.invoke(instance, value);
            return result;
        } catch (Exception e) {
            bb.writeLog("invokeMethod-e-" + e.getMessage());
            return value;
        }
    }

    private CellStyle createDataStyle(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);

        // 垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        return style;
    }

    private CellStyle createHeaderStyle(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();

        // 设置字体
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);

        // 设置背景色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);

        // 居中对齐
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }


    private String concatSqlWhere(String key, String selectname, String sqlWhere, Map<String, Object> paramMap, HashSet<String> selectFieldSet, String fieldType) {
        //0字符串 1数字 2日期范围 3模糊查询
        if (ObjectUtil.equal(fieldType, "2")) {
            selectFieldSet.add(selectname);
            String start = (String) paramMap.get(key + "Start");
            String end = (String) paramMap.get(key + "End");
            if (StringUtils.isNotEmpty(start) && StringUtils.isNotEmpty(end)) {
                sqlWhere += " (" + key + ">='" + start + "' AND " + key + " <='" + end + "') AND";
            }
        }
        String value = (String) paramMap.get(key);
        if (StringUtils.isNotEmpty(value)) {
            selectFieldSet.add(selectname);
            if (ObjectUtil.equal(fieldType, "0")) {
                sqlWhere += " " + key + "='" + value + "' AND";
            } else if (ObjectUtil.equal(fieldType, "1")) {
                sqlWhere += " " + key + "=" + value + " AND";
            } else if (ObjectUtil.equal(fieldType, "3")) {
                sqlWhere += " " + key + " like '%" + value + "%' AND";
            }
        }
        return sqlWhere;
    }

    @Override
    public Map<String, Object> listPage(Map<String, Object> paramMap) {
        BaseBean bb = new BaseBean();
        RecordSet rs = new RecordSet();
        Map<String, Object> resultMap = new HashMap<>(4);
        //获取显示字段
        Map<String, Object> fieldsMap = getFields(user, rs);
        String fields = (String) fieldsMap.get("fields");
        HashSet<String> selectFieldSet = (HashSet<String>) fieldsMap.get("selectFieldSet");
        if (StringUtils.isEmpty(fields)) {
            resultMap.put("msg", "fields is null");
            return resultMap;
        }
        //员工id，是否页面展示，但需要接口返回该值用于点击跳转
        selectFieldSet.add("t1.id as userid");
        fields += ",userid";

        String pageId = String.valueOf(UUID.randomUUID());
        //给字段排个顺序
//        updateOrderSelect(user, pageId);

        String sqlWhere = getSqlWhere(rs, paramMap, selectFieldSet);
        if (StringUtils.isEmpty(sqlWhere)) {
            return resultMap;
        }
        String fromSql = getSqlFrom(paramMap, selectFieldSet);
        try {
            //字段对照关系
            HashMap<String, Map<String, String>> fieldMap = new HashMap<>();
            String sql = "select id,chinese,dataName,transMethod from uf_roster_field";
            rs.execute(sql);
            while (rs.next()) {
                HashMap<String, String> map = new HashMap<>();
                map.put("chinese", rs.getString("chinese"));
                map.put("transMethod", rs.getString("transMethod"));
                fieldMap.put(rs.getString("dataName"), map);
            }

            WeaTable table = new WeaTable();
            table.setPageID(pageId);
            table.setPageUID(String.format("%s_%s", pageId, user.getUID()));
            String pageSize = PageIdConst.getPageSize(pageId, user.getUID());
            table.setPagesize(pageSize);
            table.setBackfields(fields);
            table.setSqlform(fromSql);
            table.setSqlwhere(sqlWhere);
            table.setSqlprimarykey("userid");
            table.setSqlorderby((String) fieldsMap.get("orderFields"));
            table.setSqlsortway("ASC");
            table.setMergeOrder(WeaBoolAttr.TRUE);
//            bb.writeLog("customSql-" + fields + fromSql + sqlWhere);
            //将当前人员的查询sql存到数据库以便导出使用
            saveExportSql(fromSql, sqlWhere, (String) fieldsMap.get("orderFields"), user);

            String[] split = fields.split(",");
            LinkedList<WeaTableColumn> weaTableColumns = new LinkedList<>();
            for (String tableKey : split) {
                String lk = "10%";
                String transMethod = fieldMap.get(tableKey).get("transMethod");
                String chinese = fieldMap.get(tableKey).get("chinese");
                WeaTableColumn column = new WeaTableColumn(lk, chinese, tableKey).setTransmethod(transMethod);
//                if (orderFields.contains(tableKey)) {
//                    column.setOrderkey(tableKey);
//                }
                if ("userid".equals(tableKey)) {
                    column.setIsPrimarykey(WeaBoolAttr.TRUE);
                    column.setDisplay(WeaBoolAttr.FALSE);
                    column.setHide(WeaBoolAttr.TRUE);
                }
                weaTableColumns.add(column);
            }

            table.setColumns(weaTableColumns);
            table.setTableType(WeaTableType.NONE);
            WeaResultMsg result = new WeaResultMsg(false);
            result.putAll(table.makeDataResult());
            result.success();
            resultMap.putAll(result.getResultMap());
        } catch (Exception e) {
            bb.writeLog("CustomRosterService-e-" + e);
            return resultMap;
        }
        return resultMap;
    }

    private void saveExportSql(String fromSql, String sqlWhere, String orderFields, User user) {
        try {
            RecordSet rs = new RecordSet();
            String sql = "select id from uf_roster_tempfield where userid=?";
            String sqlTemp = fromSql + " " + sqlWhere + " order by " + orderFields + " asc";
            rs.executeQuery(sql, user.getUID());
            if (rs.next()) {
                rs.executeUpdate("update uf_roster_tempfield set sql=? where userid=?", sqlTemp, user.getUID());
            } else {
                rs.executeUpdate("insert into uf_roster_tempfield (userid,sql) values(?,?)", user.getUID(), sqlTemp);
            }
        } catch (Exception e) {
            bb.writeLog("saveExportSql-e-" + e.getMessage());
        }
    }

    @NotNull
    private String getSqlWhere(RecordSet rs, Map<String, Object> paramMap, HashSet<String> selectFieldSet) {
        StringBuilder subcompanyids = new StringBuilder();
        StringBuilder departmentids = new StringBuilder();
        String searchTxt = (String) paramMap.get("searchTxt");
        String bdhgwmc = (String) paramMap.get("bdhgwmc");
        String status = (String) paramMap.get("status");
        String checkedDatas = (String) paramMap.get("checkedDatas");
        String leaveStart = (String) paramMap.get("leaveStart");
        String leaveEnd = (String) paramMap.get("leaveEnd");
        RecordSet rs2 = new RecordSet();
        RecordSet rs3 = new RecordSet();
        String sqlWhere = "";
        //高级搜索查询字段
        rs.execute("select dataName,selectname,fieldtype from uf_roster_field where isadvsearch=0");
        while (rs.next()) {
            String dataName = rs.getString("dataName");
            String selectname = rs.getString("selectname");
            String fieldtype = rs.getString("fieldtype");
            sqlWhere = concatSqlWhere(dataName, selectname, sqlWhere, paramMap, selectFieldSet, fieldtype);
        }
        //查询当前用户可见的部门及分部
        HashSet<String> authDepartment = getAuthDepartment(rs, checkedDatas, user, rs2, rs3);
        if (CollectionUtil.isEmpty(authDepartment)) {
            return "";
        }

        for (String checked : authDepartment) {
            String[] split = checked.split("__");
            //分部
            if (split[0].equals("1")) {
                subcompanyids.append("'").append(split[1]).append("',");
            } else {
                //部门
                departmentids.append("'").append(split[1]).append("',");
            }
        }
        if (StringUtils.isNotEmpty(subcompanyids)) {
            String subcompanyidsStr = subcompanyids.substring(0, subcompanyids.lastIndexOf(","));
            sqlWhere += " ((FB1 in (" + subcompanyidsStr + ") or FB2 in (" + subcompanyidsStr + ") or FB3 in (" + subcompanyidsStr + "))";
            if (StringUtils.isNotEmpty(departmentids)) {
                sqlWhere += " OR ";
            } else {
                sqlWhere += " ) AND ";
            }
            selectFieldSet.add("t9.FB1");
            selectFieldSet.add("t9.FB2");
            selectFieldSet.add("t9.FB3");
        }
        if (StringUtils.isNotEmpty(departmentids)) {
            String departmentidsStr = departmentids.substring(0, departmentids.lastIndexOf(","));
            sqlWhere += " (BM1 in (" + departmentidsStr + ") or BM2 in (" + departmentidsStr + ") or BM3 in (" + departmentidsStr + "))" + " AND";
            selectFieldSet.add("t9.BM1");
            selectFieldSet.add("t9.BM2");
            selectFieldSet.add("t9.BM3");
        }

        if (StringUtils.isNotEmpty(status)) {
            sqlWhere += " status in (" + status + ") AND";
            selectFieldSet.add("t8.bdhzt AS \"status\"");
        }
        if (StringUtils.isNotEmpty(leaveStart) && StringUtils.isNotEmpty(leaveEnd)) {
            sqlWhere += " lzrq>='" + leaveStart + "' and lzrq<='" + leaveEnd + "' AND";
            selectFieldSet.add("t14.lzrq");
            selectFieldSet.add("t14.lzrq");
        }
        if (StringUtils.isNotEmpty(searchTxt)) {
            sqlWhere += " (lastname like '%" + searchTxt + "%' " + " or workcode like '%" + searchTxt + "%' " + " or certificatenum like '%" + searchTxt + "%' or mobile like '%" + searchTxt + "%') ";
            selectFieldSet.add("t1.lastname");
            selectFieldSet.add("t1.workcode");
            selectFieldSet.add("t1.certificatenum");
            selectFieldSet.add("t1.mobile");
        }
        //岗位名称文本多模糊查询
        if (StringUtils.isNotEmpty(bdhgwmc)) {
            String[] split1 = bdhgwmc.split(" ");
            String sqlLike = "(";
            for (String s : split1) {
                sqlLike += "bdhgwmc like '%" + s.trim() + "%' or ";
            }
            String removeLastOr = sqlLike.substring(0, sqlLike.lastIndexOf("or")) + ")";
            sqlWhere += removeLastOr;
            selectFieldSet.add("t8.bdhgwmc");
        }
        //添加where
        if (StringUtils.isNotEmpty(sqlWhere)) {
            sqlWhere = " where " + sqlWhere;
        }
        //去除最后一个拼接
        if (sqlWhere.endsWith("AND")) {
            sqlWhere = sqlWhere.substring(0, sqlWhere.lastIndexOf("AND"));
        }
        if (sqlWhere.endsWith("OR")) {
            sqlWhere = sqlWhere.substring(0, sqlWhere.lastIndexOf("OR"));
        }
        return sqlWhere;
    }

    private HashSet<String> getAuthDepartment(RecordSet rs, String checkedDatas, User user, RecordSet rs2, RecordSet rs3) {
        HashSet<String> querySet = new HashSet<>();
        //当前用户所在的角色
        String sql = "select concat( ',',roleid,',')as roleid from HrmRoleMembers where resourceid=?";
        rs2.executeQuery(sql, user.getUID());
        while (rs2.next()) {
            String roleid = rs2.getString("roleid");
            String querySql = "select kjfb,kjbm from uf_roster_authority " + "where concat(',',roles,',') like '%" + roleid + "%' or  concat(',',ry,',') like '%," + user.getUID() + ",%'";
            rs3.executeQuery(querySql);
            while (rs3.next()) {
                //分部、部门
                String kjfb = rs3.getString("kjfb");
                String kjbm = rs3.getString("kjbm");
                if (StringUtils.isNotEmpty(kjfb)) {
                    String[] split = kjfb.split(",");
                    for (String s : split) {
                        String kjfbStr = "1__" + s;
                        querySet.add(kjfbStr);
                    }
                }
                if (StringUtils.isNotEmpty(kjbm)) {
                    String[] split = kjbm.split(",");
                    for (String s : split) {
                        String kjbmStr = "2__" + s;
                        querySet.add(kjbmStr);
                    }
                }
            }
        }
        bb.writeLog("getAuthDepartment-querySet-" + querySet);
        if (querySet.size() > 0) {
            if (StringUtils.isEmpty(checkedDatas)) {
                checkedDatas = String.join(",", querySet);
                return querySet;
            } else {
                sql = "select a.subcompanyid1,b.supsubcomid from hrmdepartment a \n" + "left join HrmSubCompany b on b.id=a.subcompanyid1 \n" + "where a.id=?";
                String supSql = "select a.id,a.supsubcomid,b.supsubcomid as supsubcomid2 from HrmSubCompany a \n" + "left join HrmSubCompany b on b.id= a.supsubcomid\n" + "where a.id=?";
                List<String> list = Arrays.asList(checkedDatas.split(","));
                HashSet<String> set = new HashSet<>();
                //查询出部门的上级分部
                for (String item : list) {
                    if (querySet.contains(item)) {
                        set.add(item);
                    }
                    if (item.startsWith("2__")) {
                        rs.executeQuery(sql, item.substring(item.lastIndexOf("_") + 1));
                        if (rs.next()) {
                            String subcompanyid = "1__" + rs.getString("subcompanyid1");
                            String supsubcomid = "1__" + rs.getString("supsubcomid");
                            if ((querySet.contains(subcompanyid) || querySet.contains(supsubcomid))) {
                                set.add(item);
                                if (list.contains(subcompanyid)) {
                                    set.add(subcompanyid);
                                }
                                if (list.contains(supsubcomid)) {
                                    set.add(supsubcomid);
                                }
                            }
                        }
                    } else {
                        //分部的上级分部
                        rs.executeQuery(supSql, item.substring(item.lastIndexOf("_") + 1));
                        if (rs.next()) {
                            String supsubcomid = "1__" + rs.getString("supsubcomid");
                            String supsubcomid2 = "1__" + rs.getString("supsubcomid2");
                            if (querySet.contains(supsubcomid) || querySet.contains(supsubcomid2)) {
                                set.add(item);
                                if (list.contains(supsubcomid)) {
                                    set.add(supsubcomid);
                                }
                                if (list.contains(supsubcomid2)) {
                                    set.add(supsubcomid2);
                                }
                            }
                        }
                    }
                }
                bb.writeLog("getAuthDepartment-set-" + set);
//                set.retainAll(querySet);
                return set;
            }
        }
        return querySet;
    }

    @Override
    public LinkedList<TemplateFieldsPo> customFieldMap() {
        LinkedList<TemplateFieldsPo> linkedList = new LinkedList<TemplateFieldsPo>();
        try {
            String compareId = "," + user.getUID() + ",";
            String compareDept = "," + user.getUserDepartment() + ",";
            String compareCompany = "," + user.getUserSubCompany1() + ",";
            RecordSet rs = new RecordSet();
            RecordSet rs2 = new RecordSet();
            //查询默认可展示字段
            String sql = "select id,chinese,dataName,defaultShow,zdjs,zdrlzy,zdfb,zdbm from uf_roster_field order by id asc";
            rs.executeQuery(sql);
            while (rs.next()) {
                String defaultShow = rs.getString("defaultShow");
                String roleIds = rs.getString("zdjs");
                String members = "," + rs.getString("zdrlzy") + ",";
                String companyIds = rs.getString("zdfb");
                String departments = rs.getString("zdbm");
                TemplateFieldsPo fieldsPo = TemplateFieldsPo.builder().id(rs.getString("id")).fieldName(rs.getString("chinese")).fieldDataName(rs.getString("dataName")).build();
                //默认显示的字段
                if ("0".equals(defaultShow)) {
                    linkedList.add(fieldsPo);
                } else {
                    if (members.contains(compareId)) {
                        linkedList.add(fieldsPo);
                        continue;
                    }
                    //当前人员的部门和分部，是否在权限下
                    String s1 = "," + companyIds + ",";
                    String s2 = "," + departments + ",";
                    if (s1.contains(compareCompany) || s2.contains(compareDept)) {
                        linkedList.add(fieldsPo);
                        continue;
                    }
                    //查询角色成员中是否当前人员
                    if (StringUtils.isNotEmpty(roleIds) && isRoleContainsCurrentUser(rs2, roleIds)) {
                        linkedList.add(fieldsPo);
                    }
                }
            }
        } catch (Exception e) {
            bb.writeLog("customFields-e-" + e.getMessage());
        }
        return linkedList;
    }

    private boolean isRoleContainsCurrentUser(RecordSet rs2, String roleIds) {
        String sql = "select resourceid from HrmRoleMembers where roleid in(" + roleIds + ")  and resourceid=?";
        rs2.executeQuery(sql, user.getUID());
        if (rs2.next()) {
            return true;
        }
        return false;
    }

    private Map<String, Object> getFields(User user, RecordSet rs) {
        Map<String, Object> map = new HashMap<>();
        LinkedList<String> fieldsLinked = new LinkedList<>();
        HashSet<String> selectFieldSet = new HashSet<>();

        String orderSelectName = "IIF(t1.seclevel >= 60, '总部', t9.SYZZ) AS SYZZ&&t9.FB1&&t9.FB2&&t9.BM1&&t9.BM2&&t9.BM3&&t1.workcode";
        String orderDataName = "SYZZ,FB1,FB2,BM1,BM2,BM3,WORKCODE";
        //排序字段
        for (String selectName : orderSelectName.split("&&")) {
            selectFieldSet.add(selectName);
        }
        map.put("orderFields", orderDataName);

//        StringBuilder orderFields = new StringBuilder();
//        String sql = "select dataName,selectname from uf_roster_field where sfpxzd=0";
//        rs.execute(sql);
//        while (rs.next()) {
//            String dataName = rs.getString("dataName");
//            orderFields.append(dataName).append(",");
//            fieldsLinked.add(dataName);
//            selectFieldSet.add(rs.getString("selectname"));
//        }
//        map.put("orderFields", orderFields.substring(0, orderFields.lastIndexOf(",")));

        String sql = "";
        //先查询是否有模板，有则取模板字段
        LinkedList<CustomTemplateVo> customTemplate = getService(user).getCustomTemplate();
        List<CustomTemplateVo> filteredList = customTemplate.stream().filter(vo -> vo.getIsused() != null && vo.getIsused() == 1).collect(Collectors.toList());
        if (filteredList.size() > 0) {
            CustomTemplateVo templateVo = filteredList.get(0);
            //查询模板对应字段
            sql = "select dataName,selectname from uf_roster_field where id =?";
            for (String fieldId : templateVo.getTemplateFields().split(",")) {
                rs.executeQuery(sql, fieldId);
                if (rs.next()) {
                    String dataName = rs.getString("dataName");
                    if (!fieldsLinked.contains(dataName)) {
                        fieldsLinked.add(dataName);
                    }
                    selectFieldSet.add(rs.getString("selectname"));
                }
            }
        } else {
            //取默认显示字段
            sql = "select dataName,selectname from uf_roster_field where defaultShow=0";
            rs.executeQuery(sql);
            while (rs.next()) {
                String dataName = rs.getString("dataName");
                if (!fieldsLinked.contains(dataName)) {
                    fieldsLinked.add(dataName);
                }
                selectFieldSet.add(rs.getString("selectname"));
            }
        }
        map.put("fields", String.join(",", fieldsLinked));
        map.put("selectFieldSet", selectFieldSet);
        return map;
    }

    private String getSqlFrom(Map<String, Object> paramMap, HashSet<String> fileds) {
        String referDate = (String) paramMap.get("referDate");
        String selectFields = String.join(", ", fileds);
        String fromSql = " from " + "(select " + selectFields + " FROM hrmresource t1\n" + "LEFT JOIN cus_fielddata t3 ON t3.id = t1.id AND t3.scopeid = 3\n" + "LEFT JOIN cus_fielddata t4 ON t4.id = t1.id AND t4.scopeid = 1\n" + "LEFT JOIN uf_cadre t10 ON t10.yg = t1.id\n" + "LEFT JOIN uf_roster_htxy t5 ON t5.ygxm = t1.id\n" + "LEFT JOIN uf_roster_zlxx t6 ON t6.xm = t1.id\n" + "LEFT JOIN uf_roster_jjlxrxx t7 ON t7.xm = t1.id \n" + "LEFT JOIN uf_rzjl t8 ON t1.id = t8.yg AND t8.shenxrq<='" + referDate + "' and (t8.sxrq>'" + referDate + "' or t8.sxrq is null)\n" + "LEFT JOIN uf_roster_work t9 on t9.did=t8.id\n" + "LEFT JOIN (\n" + "    SELECT *\n" + "    FROM formtable_main_35 t14a\n" + "    WHERE id = (\n" + "        SELECT MAX(id) \n" + "        FROM formtable_main_35 t14b \n" + "        WHERE t14b.sqr = t14a.sqr\n" + "    )\n" + ") t14 ON t1.id = t14.sqr\n" + "LEFT JOIN workflow_requestbase tbase on tbase.requestId=t14.requestId and tbase.currentnodetype = 3\n" + "LEFT JOIN " + "(\n" + "    SELECT t15a.*\n" + "    FROM uf_khbhqxxrg t15a\n" + "    WHERE t15a.lx = 0\n" + "    AND (t15a.jsrq IS NULL OR t15a.id = (\n" + "        SELECT TOP 1 t15b.id\n" + "        FROM uf_khbhqxxrg t15b\n" + "        WHERE t15b.ygxm = t15a.ygxm \n" + "        AND t15b.lx = 0\n" + "        AND t15b.ksrq <= '" + referDate + "'\n" + "        AND (t15b.jsrq >= '" + referDate + "' OR t15b.jsrq IS NULL)\n" + "        ORDER BY CASE WHEN t15b.jsrq IS NULL THEN 1 ELSE 0 END, t15b.jsrq DESC\n" + "    ))\n" + ") t15 ON t1.id = t15.ygxm\n" + "LEFT JOIN uf_roster_rzlc t16 ON t16.rzryid = t1.id\n" + "LEFT JOIN formtable_main_71 t17 ON t17.requestId = t16.yglydx\n" + "OUTER APPLY (\n" + "        SELECT TOP 1 * \n" + "        FROM uf_yhk \n" + "        WHERE xm = t1.id AND yt LIKE '%0%' AND (qyzt IS NULL OR qyzt=0)\n" + "        ORDER BY NEWID()\n" + "    ) t18\n" + "WHERE ISNUMERIC(t1.workcode) = 1 AND (t1.accounttype is null or t1.accounttype=0)" + ") tt";
        return fromSql;
    }
}
